package com.ruoyi.hualian.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.write.style.column.LongestMatchColumnWidthStyleStrategy;
import com.alibaba.fastjson2.JSON;
import com.ruoyi.common.annotation.Excel;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.utils.http.HttpUtils;
import com.ruoyi.hualian.domain.*;
import com.ruoyi.hualian.service.*;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import io.swagger.annotations.ApiParam;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.time.temporal.WeekFields;
import java.util.*;

/**
 * 华链 ERP系统 Excel表格处理功能接口
 *
 * @author hujian
 */
@Api(tags = "Excel表功能")
@RestController
@RequestMapping("huaLian/excel")
public class HuaLianExcelController {

    private static final Logger log = LoggerFactory.getLogger(HttpUtils.class);

    @Autowired
    private ExcelService excelService;

    @Autowired
    private IHlTimingPulleyService hlTimingPulleyService;

    @Autowired
    private IHlToothService hlToothService;

    /**
     * 同步轮Excel模板导入数据库
     */
    @ApiOperation("同步轮Excel模板导入数据库")
    @PostMapping("/excelTimingPulley")
    public AjaxResult excelTimingPulley(@ApiParam(name = "insertDatabase", value = "数据是否插入数据库", required = true) @RequestParam("insertDatabase") Boolean insertDatabase) throws Exception {
        List<String> stringList = new ArrayList<>();

        // BK
        // String TP_H3M = "TP_H3M";
        // stringList.add(TP_H3M);
        // String TP_S2M = "TP_S2M";
        // stringList.add(TP_S2M);
        String TP_S3M = "TP_S3M";
        stringList.add(TP_S3M);
        String TP_MXL = "TP_MXL";
        stringList.add(TP_MXL);
        // Not BK
        // String TP_H5M = "TP_H5M";
        // stringList.add(TP_H5M);
        // String TP_H8M = "TP_H8M";
        // stringList.add(TP_H8M);
        // String TP_S5M = "TP_S5M";
        // stringList.add(TP_S5M);
        // String TP_S8M = "TP_S8M";
        // stringList.add(TP_S8M);
        // String TP_XL = "TP_XL";
        // stringList.add(TP_XL);
        // String TP_L = "TP_L";
        // stringList.add(TP_L);
        // String TP_H = "TP_H";
        // stringList.add(TP_H);
        // String TP_T5 = "TP_T5";
        // stringList.add(TP_T5);
        // String TP_T10 = "TP_T10";
        // stringList.add(TP_T10);

        String filePath = "D:\\华链-胡健\\2023-12-30成本价格的计算\\加工后的Excel表\\";
        String fileName = "TP_S3M.xlsx";
        boolean containBK = true;
        for (String string : stringList) {
            fileName = string + ".xlsx";
            List<HlTimingPulley> hlTimingPulleys = excelService.excelObjectList(HlTimingPulley.class, filePath, fileName, false);

            hlTimingPulleyService.excelTimingPulley(hlTimingPulleys, containBK, insertDatabase);
        }

        /*
            List<HlTimingPulley> hlTimingPulleys = excelService.read(file, HlTimingPulley.class);
         */
        return AjaxResult.success("操作成功");
    }

    /**
     * 带齿惰轮Excel模板导入数据库
     */
    @ApiOperation("带齿惰轮Excel模板导入数据库")
    @PostMapping("/excelIgTimingPulley")
    public AjaxResult excelIgTimingPulley(@ApiParam(name = "insertDatabase", value = "数据是否插入数据库", required = true) @RequestParam("insertDatabase") Boolean insertDatabase) throws Exception {
        List<String> stringList = new ArrayList<>();

        // String IG_S2M = "IG_S2M";
        // stringList.add(IG_S2M);
        // String IG_S3M = "IG_S3M";
        // stringList.add(IG_S3M);
        // String IG_S5M = "IG_S5M";
        // stringList.add(IG_S5M);
        // String IG_S8M = "IG_S8M";
        // stringList.add(IG_S8M);
        // String IG_T5 = "IG_T5";
        // stringList.add(IG_T5);
        // String IG_T10 = "IG_T10";
        // stringList.add(IG_T10);
        // String IG_MXL = "IG_MXL";
        // stringList.add(IG_MXL);
        // String IG_XL = "IG_XL";
        // stringList.add(IG_XL);
        String IG_L = "IG_L";
        stringList.add(IG_L);
        String IG_H = "IG_H";
        stringList.add(IG_H);

        String filePath = "D:\\华链-胡健\\2023-12-30成本价格的计算\\加工后的Excel表\\";
        String fileName = "TP_S3M.xlsx";
        boolean containBK = false;
        for (String string : stringList) {
            fileName = string + ".xlsx";
            List<HlTimingPulley> hlTimingPulleys = excelService.excelObjectList(HlTimingPulley.class, filePath, fileName, false);
            hlTimingPulleyService.excelTimingPulley(hlTimingPulleys, containBK, insertDatabase);
        }

        /*
            List<HlTimingPulley> hlTimingPulleys = excelService.read(file, HlTimingPulley.class);
         */
        return AjaxResult.success("操作成功");
    }

    /**
     * 同步轮Excel表数据导入ERP系统
     * <p>
     * Excel表的创建
     */
    // @ApiOperation("同步轮Excel表数据导入ERP系统")
    // @PostMapping("/excelErp")
    public AjaxResult excelErp(MultipartFile file,
                               @RequestParam("filePath") String filePath, @RequestParam("fileName") String fileName,
                               HttpServletRequest request, HttpServletResponse response) throws IOException, IllegalAccessException {



        // 首先获得型号
        List<ExcelImportErp> excelImportErps = excelService.excelObjectList(ExcelImportErp.class, filePath, fileName, false);

        List<ExcelImportErp> newExcelImportErps = new ArrayList<ExcelImportErp>();

        // 一级分类、二级分类、三级分类
        filePath = "D:\\华链-胡健\\2023-12-27ERP系统Excel数据表导入ERP系统\\加工后的Excel表\\";
        fileName = "商品分类.xlsx";
        List<ExcelImportErp> commodityLevelExcelImportErps = excelService.excelObjectList(ExcelImportErp.class, filePath, fileName, false);

        // 商品名称
        filePath = "D:\\华链-胡健\\2023-12-27ERP系统Excel数据表导入ERP系统\\加工后的Excel表\\";
        fileName = "商品名称.xlsx";
        List<ExcelImportErp> commodityNameExcelImportErps = excelService.excelObjectList(ExcelImportErp.class, filePath, fileName, false);

        // 原始数据处理
        for (int i = 0; i < excelImportErps.size(); i++) {

            ExcelImportErp oldExcelImportErp = excelImportErps.get(i);


            ExcelImportErp newExcelImportErp = new ExcelImportErp();

            // 处理商品编码
            String commodityCode = excelImportErps.get(i).getType();


            // 商品编码为空跳过此次循环
            if (ObjectUtils.isEmpty(commodityCode)) {
                log.error("商品编码为空");
                continue;
            }

            /*
                总共情况
                ABC123 ABC123-456
                AHH605 AHHS605 AHHS678-30
             */

            // 处理字符串拼接，速度快点
            StringBuilder stringBuffer = new StringBuilder();
            StringBuilder oldSb = new StringBuilder();
            StringBuilder newSb = new StringBuilder();
            String type = null;

            // 所有的商品编码进行了 '-' 符号的处理
            Map<String, StringBuilder> map = commodityCodeHandle(stringBuffer, commodityCode);
            commodityCode = map.get("commodityCode").toString();
            String specs = map.get("specs").toString();

            String[] split = commodityCode.split("-");

            // ABC-123 AHH-605 AHH-S605 BFS623
            if (split.length == 2) {
                // 再进行添加 ZZ 处理
                type = oldSb.append(commodityCode).append("ZZ").toString();
            }
            // ABC-123-456
            else {
                for (int ii = 0; ii < split.length; ii++) {
                    if (ii != split.length - 1) {
                        oldSb.append(split[ii]);
                        if (ii == 1) {
                            oldSb.append("ZZ");
                        }
                        oldSb.append("-");
                    } else {
                        oldSb.append(split[ii]);
                    }
                }
                type = oldSb.toString();
            }
            // 商品编码和型号、M-、规格
            oldExcelImportErp.setCommodityCode(type);
            oldExcelImportErp.setType(type);
            oldExcelImportErp.setSpecs(specs);

            // M- 处理
            type = newSb.append("M-").append(commodityCode).toString();
            newExcelImportErp.setCommodityCode(type);
            newExcelImportErp.setType(type);
            newExcelImportErp.setSpecs(specs);

            // 商品分类处理
            String specsLevel = null;
            if (specs.length() == 1) {
                specsLevel = specs;
            } else if (!ObjectUtils.notEqual("TBHS", specs)) {
                specsLevel = specs.substring(0, specs.length() - 1);
            } else if (specs.contains("HHS")) {
                specsLevel = specs.substring(0, specs.length() - 2);
            } else if (specs.contains("HS") && specs.length() >= 4) {
                specsLevel = specs.substring(0, specs.length() - 2);
            } else {
                specsLevel = specs.substring(0, specs.length() - 1);
            }
            for (int ii = 0; ii < commodityLevelExcelImportErps.size(); ii++) {
                ExcelImportErp excelImportErp = commodityLevelExcelImportErps.get(ii);
                String threeLevel = excelImportErp.getThreeLevel();
                String twoLevel = excelImportErp.getTwoLevel();
                // 首先与三级分类进行对标
                // 三级分类不为空
                if (ObjectUtils.isNotEmpty(threeLevel)) {
                    String[] threeSplit = threeLevel.split(" ");
                    if (!ObjectUtils.notEqual(threeSplit[0], specsLevel)) {
                        oldTransferNew(oldExcelImportErp, excelImportErp);
                        oldTransferNew(newExcelImportErp, excelImportErp);
                        break;
                    }
                    continue;
                }
                // 三级分类为空
                else {
                    String[] twoSplit = twoLevel.split(" ");
                    if (twoSplit[0].contains("-")) {
                        String[] towSplitSplit = twoSplit[0].split("-");
                        for (String s : towSplitSplit) {
                            if (!ObjectUtils.notEqual(s.substring(0, s.length() - 1), specsLevel)) {
                                oldTransferNew(oldExcelImportErp, excelImportErp);
                                oldTransferNew(newExcelImportErp, excelImportErp);
                                break;
                            }
                        }
                    } else {
                        if (!ObjectUtils.notEqual(twoSplit[0].substring(0, twoSplit[0].length() - 1), specsLevel)) {
                            oldTransferNew(oldExcelImportErp, excelImportErp);
                            oldTransferNew(newExcelImportErp, excelImportErp);
                            break;
                        }
                    }
                }
            }

            for (int ii = 0; ii < commodityNameExcelImportErps.size(); ii++) {
                if (!ObjectUtils.notEqual(specs, commodityNameExcelImportErps.get(ii).getSpecs())) {
                    oldExcelImportErp.setCommodityName(commodityNameExcelImportErps.get(ii).getCommodityName());
                    newExcelImportErp.setCommodityName(commodityNameExcelImportErps.get(ii).getCommodityName());
                }
            }

            newExcelImportErps.add(oldExcelImportErp);
            newExcelImportErps.add(newExcelImportErp);
        }

        M(newExcelImportErps);

        // 数据导出到Excel表
        /*
            Workbook wb = new XSSFWorkbook();
            Sheet sheet1 = wb.createSheet("ERP数据导入表");
            Row row;
            Cell cell;
            for (int i = 0; i < newExcelImportErps.size(); i++) {
                ExcelImportErp erpImportExcel = newExcelImportErps.get(i);
                row = sheet1.createRow(i);
                Field[] fields = ExcelImportErp.class.getDeclaredFields();
                for (int i1 = 0; i1 < fields.length; i1++) {
                    //反射时让私有变量变成可访问
                    fields[i1].setAccessible(true);
                    cell = row.createCell(i1);
                    Object o = fields[i1].get(erpImportExcel);

                    if (ObjectUtils.isEmpty(o)) {
                        cell.setCellValue("");
                    }
                    else {
                        cell.setCellValue(o.toString());
                    }
                }
            }
            FileOutputStream out = new FileOutputStream("D:\\华链-胡健\\2023-12-27ERP系统Excel数据表导入ERP系统\\加工后的Excel表\\ERP数据导入Excel表.xlsx");
            wb.write(out);
            out.close();
            wb.close();
        */

        // 下载的地址以及名称
        String path = "D:\\华链-胡健\\2023-12-27ERP系统Excel数据表导入ERP系统\\加工后的Excel表\\ERP数据导入Excel表";
        // 核心代码
        EasyExcel.write(path + ".xlsx", ExcelImportErp.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet().doWrite(newExcelImportErps);
        return AjaxResult.success("Excel表导出成功");
    }

    /**
     * 对原生型号进行处理得到想要的型号格式
     *
     * @param commodityCode 商品编码，对此要进行加工
     */
    public Map<String, StringBuilder> commodityCodeHandle(StringBuilder stringBuilder, String commodityCode) {
        StringBuilder specs = new StringBuilder();

        int i = 0;
        for (; i < commodityCode.length(); i++) {
            specs.append(commodityCode.charAt(i));
            // 一直循环遍历型号字符串，直到出现数字的时候
            if (!((commodityCode.charAt(i) >= 'a' && commodityCode.charAt(i) <= 'z') || (commodityCode.charAt(i) >= 'A' && commodityCode.charAt(i) <= 'Z'))) {
                // 如果前一个字符是 S 且 当前字符位数大于 3 AHHS605-123 AHS605 BKS605 BFS623
                if (commodityCode.charAt(i - 1) == 'S' && i >= 3) {
                    // 如果再前一个字符是 H 且 当前位数 等于 3
                    if (commodityCode.charAt(i - 2) == 'H') {
                        if (i == 3) {
                            // AHS605
                            // String str1 = commodityCode.substring(0, i);  AHS
                            // String str2 = commodityCode.substring(i); 605
                            stringBuilder.append(commodityCode, 0, i).append("-").append(commodityCode.substring(i));
                            break;
                        } else {
                            // AHHS605-123
                            // String str1 = commodityCode.substring(0, i - 1); AHH
                            // String str2 = commodityCode.substring(i - 1); S605-123
                            stringBuilder.append(commodityCode, 0, i - 1).append("-").append(commodityCode.substring(i - 1));
                            break;
                        }
                    } else {
                        stringBuilder.append(commodityCode, 0, i).append("-").append(commodityCode.substring(i));
                    }
                } else {
                    // 都不是 ABC123 ABC123-456
                    // String str1 = commodityCode.substring(0, i);  ABC
                    // String str2 = commodityCode.substring(i); 123-456
                    stringBuilder.append(commodityCode, 0, i).append("-").append(commodityCode.substring(i));
                }
                break;
            }
        }
        Map<String, StringBuilder> map = new HashMap<>();
        map.put("specs", new StringBuilder(specs.substring(0, i)));
        map.put("commodityCode", stringBuilder);
        return map;
    }

    /**
     * 分类数据转移
     */
    public void oldTransferNew(ExcelImportErp newExcelImportErp, ExcelImportErp oldExcelImportErp) {
        newExcelImportErp.setOneLevel(oldExcelImportErp.getOneLevel());
        newExcelImportErp.setTwoLevel(oldExcelImportErp.getTwoLevel());
        newExcelImportErp.setThreeLevel(oldExcelImportErp.getThreeLevel());
    }

    /**
     * M- 特殊处理
     * HS 和 HHS 只会新增一个 M- + 商品编号的数据
     * 代码实现是所有的进行了 M- 的数据新增，再然后剔除
     */
    public void M(List<ExcelImportErp> newExcelImportErps) {
        // BHH	    BHH-623ZZ
        // BHH	    M-BHH-623
        // BHHS 	BHH-S623ZZ
        // BHHS 	M-BHH-S623

        List<String> stringList = new ArrayList<>();
        // 首先筛选出含有 H-S 且 含有 M- 规格的数据
        // 提出 M-BHH-S623 这种数据
        for (int i = 0; i < newExcelImportErps.size(); i++) {
            String substring = newExcelImportErps.get(i).getCommodityCode().substring(0, 2);
            boolean equals = ObjectUtils.equals("M-", substring);
            if (!equals) {
                continue;
            }
            if (newExcelImportErps.get(i).getCommodityCode().contains("H-S") && equals) {
                stringList.add(newExcelImportErps.get(i).getCommodityCode());
            }
        }

        Map<String, String> map = new HashMap<>();
        for (int i = 0; i < stringList.size(); i++) {
            StringBuilder stringBuilder = new StringBuilder();
            String[] split = stringList.get(i).split("-");
            for (int ii = 0; ii < split.length; ii++) {
                if (ii != 2) {
                    if (ii != split.length - 1) {
                        stringBuilder.append(split[ii]).append("-");
                    } else {
                        stringBuilder.append(split[ii]);
                    }
                } else {
                    stringBuilder.append(split[ii].substring(1));
                }
            }
            // M-BHH-S623
            map.put(i + "oldString", stringList.get(i));
            // M-BHH-623
            map.put(i + "newString", stringBuilder.toString());
        }

        // 删除
        List<String> deleteString = new ArrayList<>();
        for (int i = 0; i < newExcelImportErps.size(); i++) {
            for (int ii = 0; ii < stringList.size(); ii++) {
                String oldString = map.get(ii + "oldString");
                String newString = map.get(ii + "newString");
                if (ObjectUtils.equals(newExcelImportErps.get(i).getCommodityCode(), newString)) {
                    deleteString.add(oldString);
                }
            }
        }

        // 迭代器删除
        for (String string : deleteString) {
            newExcelImportErps.removeIf(o -> ObjectUtils.equals(string, o.getCommodityCode()));
        }
    }

    /**
     * OEE不良数据处理
     */
    @ApiOperation("OEE不良数据处理")
    @PostMapping("/oeePoor")
    public AjaxResult oeePoor(@RequestParam("filePath") String filePath, @RequestParam("fileName") String fileName) throws ParseException {
        List<ExcelOee> excelOeeList = excelService.excelObjectList(ExcelOee.class, filePath, fileName, false);
        List<ResultExcelOee> resultExcelOeeList = new ArrayList<>();
        log.info("Excle表数据大小 ==> {}", excelOeeList.size());

        /*
            车床线
            String workshopProductionLineOne = "车床线/车A㈠位 ①②③";
            String workshopProductionLineTwo = "车床线/车A㈡位 ④⑤⑥";
            String workshopProductionLineThree = "车床线/车B㈠位 ①②③";
            String workshopProductionLineFour = "车床线/车B㈡位 ④⑤⑥";
            String workshopProductionLineFive = "车床线/双头数车 ①②";
         */


        //铣床线
        String workshopProductionLineOne = "铣床线/铣A组 ①③⑤⑦⑨ (大切削量)";
        String workshopProductionLineTwo = "铣床线/铣A组 ②④⑥⑧ (钻安装孔)";
        String workshopProductionLineThree = "铣床线/铣A组 ⑩⑪⑫⑬⑭⑮";
        String workshopProductionLineFour = "铣床线/铣B组 ①②";

        Set<String> stringSet = new HashSet<>();
        for (int ii = 0; ii < excelOeeList.size(); ii++) {

            ExcelOee excelOee = excelOeeList.get(ii);
            String dateStr = excelOee.getDate();
            if ("日期".equals(dateStr)) {
                log.info("当Excel表中的数据多于10000条后，Sheet会自动加一，这时候标题又出现了，跳过他。此时List下标 ==> {}", ii);
                // ii = 10000
                continue;
            }
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
            Date date = simpleDateFormat.parse(dateStr);
            // 转为calendar格式
            Calendar calendar = Calendar.getInstance();
            calendar.setTime(date);
            // 如果是周日
            if (calendar.get(Calendar.DAY_OF_WEEK) == Calendar.SUNDAY) {
                calendar.add(Calendar.DAY_OF_YEAR, -1);
            }
            // 获取当前日期是当周的第i天
            int i = calendar.get(Calendar.DAY_OF_WEEK) - 1;
            // 获取当前日期所在周的第一天
            calendar.add(Calendar.DATE, -i + 1);
            Date weekStart = calendar.getTime();
            // 获取当前日期所在周的最后一天
            calendar.add(Calendar.DATE, 6);
            Date weekEnd = calendar.getTime();
            StringBuilder stringBuilder = new StringBuilder();
            stringSet.add(stringBuilder.append(new SimpleDateFormat("yyyy-MM-dd").format(weekStart)).append("至").append(new SimpleDateFormat("yyyy-MM-dd").format(weekEnd)).toString());
        }
        HashMap<String, ResultExcelOee> hashMap = new HashMap<>();
        for (String string : stringSet) {
            /*
                车床线
                StringBuilder stringBuilder = new StringBuilder();
                hashMap.put(stringBuilder.append(workshopProductionLineOne).append(string).toString(), new ResultExcelOee("0", "0", "0"));
                stringBuilder = new StringBuilder();
                hashMap.put(stringBuilder.append(workshopProductionLineTwo).append(string).toString(), new ResultExcelOee("0", "0", "0"));
                stringBuilder = new StringBuilder();
                hashMap.put(stringBuilder.append(workshopProductionLineThree).append(string).toString(), new ResultExcelOee("0", "0", "0"));
                stringBuilder = new StringBuilder();
                hashMap.put(stringBuilder.append(workshopProductionLineFour).append(string).toString(), new ResultExcelOee("0", "0", "0"));
                stringBuilder = new StringBuilder();
                hashMap.put(stringBuilder.append(workshopProductionLineFive).append(string).toString(), new ResultExcelOee("0", "0", "0"));
             */
            StringBuilder stringBuilder = new StringBuilder();
            hashMap.put(stringBuilder.append(workshopProductionLineOne).append(string).toString(), new ResultExcelOee("0", "0", "0"));
            stringBuilder = new StringBuilder();
            hashMap.put(stringBuilder.append(workshopProductionLineTwo).append(string).toString(), new ResultExcelOee("0", "0", "0"));
            stringBuilder = new StringBuilder();
            hashMap.put(stringBuilder.append(workshopProductionLineThree).append(string).toString(), new ResultExcelOee("0", "0", "0"));
            stringBuilder = new StringBuilder();
            hashMap.put(stringBuilder.append(workshopProductionLineFour).append(string).toString(), new ResultExcelOee("0", "0", "0"));
        }
        for (ExcelOee excelOee : excelOeeList) {
            String workshopProductionLine = excelOee.getWorkshopProductionLine();
            String[] workshopProductionLineSplit = workshopProductionLine.split("/");
            String workshop = workshopProductionLineSplit[0];
            String productionLine = workshopProductionLineSplit[1];

            for (String dataWeek : stringSet) {
                String dateStr = excelOee.getDate();
                if ("日期".equals(dateStr)) {
                    continue;
                }
                SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
                Date date = simpleDateFormat.parse(dateStr);

                String[] split = dataWeek.split("至");
                Date weekStart = simpleDateFormat.parse(split[0]);
                Date weekEnd = simpleDateFormat.parse(split[1]);

                LocalDate weekOfYear = LocalDate.parse(dateStr);

                // 获取周数
                int weekNum = weekOfYear.get(WeekFields.of(Locale.getDefault()).weekOfYear());

                if (date.compareTo(weekStart) >= 0 && date.compareTo(weekEnd) <= 0) {
                    BigDecimal jobNum = new BigDecimal(excelOee.getJobNum());
                    BigDecimal greenLightDuration = new BigDecimal(excelOee.getGreenLightDuration());
                    BigDecimal yellowLightDuration = new BigDecimal(excelOee.getYellowLightDuration());

                    String hashMapString = workshopProductionLine + dataWeek;
                    ResultExcelOee resultExcelOee = hashMap.get(hashMapString);

                    resultExcelOee.setWorkshopProductionLine(workshopProductionLine);
                    resultExcelOee.setDate(dataWeek);
                    resultExcelOee.setWeekNum(weekNum);

                    BigDecimal resultJobNum = new BigDecimal(resultExcelOee.getJobNum());
                    BigDecimal resultGreenLightDuration = new BigDecimal(resultExcelOee.getGreenLightDuration());
                    BigDecimal resultYellowLightDuration = new BigDecimal(resultExcelOee.getYellowLightDuration());

                    resultExcelOee.setWorkshop(workshop);
                    resultExcelOee.setProductionLine(productionLine);
                    resultExcelOee.setJobNum(jobNum.add(resultJobNum).toString());
                    resultExcelOee.setGreenLightDuration(greenLightDuration.add(resultGreenLightDuration).toString());
                    resultExcelOee.setYellowLightDuration(yellowLightDuration.add(resultYellowLightDuration).toString());

                    hashMap.put(hashMapString, resultExcelOee);
                }
            }
        }
        hashMap.forEach((key, value) -> {

            if ("0".equals(value.getJobNum())) {
                value.setJobNum("1");
            }

            BigDecimal resultJobNum = new BigDecimal(value.getJobNum());
            BigDecimal resultGreenLightDuration = new BigDecimal(value.getGreenLightDuration());
            BigDecimal resultYellowLightDuration = new BigDecimal(value.getYellowLightDuration());

            value.setGreenLightJobNum(resultGreenLightDuration.divide(resultJobNum, 2, RoundingMode.HALF_UP).toString());
            value.setYellowLightJobNum(resultYellowLightDuration.divide(resultJobNum, 2, RoundingMode.HALF_UP).toString());

            resultExcelOeeList.add(value);
        });
        log.info("处理好的数据 ==> {}", JSON.toJSONString(resultExcelOeeList));

        // 下载的地址以及名称
        String path = "D:\\华链-胡健\\2024-01-09OEE数据透视表\\加工后的Excel表\\铣床线OEE每周数据总结表";
        // 核心代码
        EasyExcel.write(path + ".xlsx", ResultExcelOee.class)
                .registerWriteHandler(new LongestMatchColumnWidthStyleStrategy())
                .sheet().doWrite(resultExcelOeeList);
        return AjaxResult.success();
    }

    /**
     * 怡和达带齿惰轮导入数据库
     */
    @ApiOperation("怡和达带齿惰轮导入数据库")
    @PostMapping("/yiHeDaIg")
    public AjaxResult yiHeDaIg(@ApiParam(name = "filePath", value = "文件路径", required = true) @RequestParam("filePath") String filePath,
                               @ApiParam(name = "fileName", value = "文件名称", required = true) @RequestParam("fileName") String fileName,
                               HttpServletResponse response) throws Exception {
        return hlTimingPulleyService.yiHeDaIg(filePath, fileName, response);
    }

    /**
     * 米思米同步轮导入数据库
     */
    @ApiOperation("米思米同步轮导入数据库")
    @PostMapping("/miSiMiTp")
    public AjaxResult miSiMiTp(@ApiParam(name = "filePath", value = "文件路径", required = true) @RequestParam("filePath") String filePath,
                               @ApiParam(name = "fileName", value = "文件名称", required = true) @RequestParam("fileName") String fileName,
                               HttpServletResponse response) throws Exception {
        return hlTimingPulleyService.miSiMiTp(filePath, fileName, response);
    }

    /**
     * 米思米带齿惰轮导入数据库
     */
    @ApiOperation("米思米带齿惰轮导入数据库")
    @PostMapping("/miSiMiIg")
    public AjaxResult miSiMiIg(@ApiParam(name = "filePath", value = "文件路径", required = true) @RequestParam("filePath") String filePath,
                               @ApiParam(name = "fileName", value = "文件名称", required = true) @RequestParam("fileName") String fileName,
                               HttpServletResponse response) throws Exception {
        return hlTimingPulleyService.miSiMiIg(filePath, fileName, response);
    }

    /**
     * 中机昌盛-华刚资料整理
     */
    @ApiOperation("中机昌盛-华刚资料整理")
    @PostMapping("/zhongJiChangShengHuaGang")
    public String zhongJiChangShengHuaGang(HttpServletResponse response) throws Exception {
        return excelService.zhongJiChangShengHuaGang(response);
    }


    /**
     * 绿灯率
     */
    @ApiOperation("绿灯率")
    @PostMapping("/green")
    public String green(@ApiParam(name = "greenFilePath", value = "报工明细路径", required = true) @RequestParam("greenFilePath") String greenFilePath,
                            @ApiParam(name = "greenFileName", value = "报工明细名称", required = true) @RequestParam("greenFileName") String greenFileName,
                            @ApiParam(name = "summaryFilePath", value = "明细汇总路径", required = true) @RequestParam("summaryFilePath") String summaryFilePath,
                            @ApiParam(name = "summaryFileName", value = "明细汇总名称", required = true) @RequestParam("summaryFileName") String summaryFileName,
                            HttpServletResponse response) throws Exception {
        return excelService.green(greenFilePath, greenFileName, summaryFilePath, summaryFileName, response);
    }

    /**
     * 货期数据导入数据库
     */
    @ApiOperation("货期数据导入数据库")
    @PostMapping("/deliveryTime")
    public AjaxResult deliveryTime (@ApiParam(name = "filePath", value = "文件路径", required = true) @RequestParam("filePath") String filePath,
                                    @ApiParam(name = "fileName", value = "文件名称", required = true) @RequestParam("fileName") String fileName,
                                    HttpServletResponse response) throws Exception {
        return excelService.deliveryTime(filePath, fileName, response);
    }

    /**
     * 皮带计算
     */
    @ApiOperation("皮带计算")
    @PostMapping("/beltCalculation")
    public AjaxResult beltCalculation(@ApiParam(name = "wheelCenterDistance", value = "轮中心距离", required = true) @RequestParam(value = "wheelCenterDistance", required = true) String wheelCenterDistance,
                                      @ApiParam(name = "tooth", value = "齿形", required = false) @RequestParam(value = "tooth", required = true) String tooth,
                                      @ApiParam(name = "timingPulleyTeethOne", value = "齿数同步轮1", required = false) @RequestParam(value = "timingPulleyTeethOne", required = true) String timingPulleyTeethOne,
                                      @ApiParam(name = "timingPulleyTeethTwo", value = "齿数同步轮2", required = false) @RequestParam(value = "timingPulleyTeethTwo", required = true) String timingPulleyTeethTwo) {

        BeltCalculation beltCalculation = new BeltCalculation();
        beltCalculation.setWheelCenterDistance(wheelCenterDistance);
        beltCalculation.setTooth(tooth);
        beltCalculation.setTimingPulleyTeethOne(timingPulleyTeethOne);
        beltCalculation.setTimingPulleyTeethTwo(timingPulleyTeethTwo);

        // 计算同步轮1 的 P.D.
        // =ROUND((H$4*VLOOKUP($H$3,$A:$D,2,0))/(PI()),2)
        wheelCenterDistance = beltCalculation.getWheelCenterDistance();
        tooth = beltCalculation.getTooth();
        timingPulleyTeethOne = beltCalculation.getTimingPulleyTeethOne();
        timingPulleyTeethTwo = beltCalculation.getTimingPulleyTeethTwo();

        // 查询 齿距 hl_tooth
        HlTooth t = new HlTooth();
        t.setTooth(tooth);
        HlTooth hlTooth = hlToothService.selectOne(t);
        if (ObjectUtils.isEmpty(hlTooth)) {
            return AjaxResult.error("齿形数据异常，请联系管理员");
        }

        BigDecimal π = new BigDecimal(Math.PI);

        // C 周长 = 齿数 X 齿距
        BigDecimal COne = hlTooth.getPitch().multiply(new BigDecimal(timingPulleyTeethOne));
        // P.D.
        BigDecimal PDOne = COne.divide(π, 3, RoundingMode.HALF_UP);

        // C 周长 = 齿数 X 齿距
        BigDecimal CTwo = hlTooth.getPitch().multiply(new BigDecimal(timingPulleyTeethTwo));
        // P.D.
        BigDecimal PDTwo = CTwo.divide(π, 2, RoundingMode.HALF_UP);

        // 注意事项
        // =IF((H5+I5)/2>H2-5,"中心距过小或同步轮过大","")
        BigDecimal judge = (PDOne.add(PDTwo)).divide(BigDecimal.valueOf(2), 2, RoundingMode.HALF_UP);
        if (judge.compareTo(new BigDecimal(wheelCenterDistance).subtract(BigDecimal.valueOf(5))) > 0) {
            return AjaxResult.error("中心距过小或同步轮过大");
        }

        // 皮带长度
        // =H5/2+I5/2+H2
        BigDecimal belt = (PDOne.divide(BigDecimal.valueOf(2), 2, RoundingMode.HALF_UP)).add(PDTwo.divide(BigDecimal.valueOf(2), 2, RoundingMode.HALF_UP)).add(new BigDecimal(wheelCenterDistance));
        return AjaxResult.success(belt);
    }


    /**
     * 法兰 FC 统计数据
     */
    @ApiOperation("法兰 FC 统计数据")
    @PostMapping("/fcStatistics")
    public String fcStatistics (@ApiParam(name = "filePath", value = "文件路径", required = true) @RequestParam("filePath") String filePath,
                                    @ApiParam(name = "fileName", value = "文件名称", required = true) @RequestParam("fileName") String fileName,
                                    HttpServletResponse response) throws Exception {
        return excelService.fcStatistics(filePath, fileName, response);
    }

    /**
     * ERP价格梯度-个性导入
     */
    @ApiOperation("ERP价格梯度-个性导入")
    @PostMapping("/priceGradient")
    public String priceGradient (@ApiParam(name = "filePath", value = "文件路径", required = true) @RequestParam("filePath") String filePath,
                                 @ApiParam(name = "fileName", value = "文件名称", required = true) @RequestParam("fileName") String fileName,
                                 HttpServletResponse response) throws Exception {
        return excelService.priceGradient(filePath, fileName, response);
    }
}
